/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package databaseLayer;

import java.sql.*;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
import javax.naming.NamingException;

import userBean.PurchaseRecord;

/**
 *
 * @author Rigsby Chi
 */
public class RecordLookUp {
    
    public static PurchaseRecord[] getPurchaseRecord(String loginName){
        PurchaseRecord[] purchaseRecord;
        try{
            
            Context initCtx = new InitialContext();
            Context envCtx = (Context)initCtx.lookup("java:comp/env");
            DataSource ds = (DataSource)envCtx.lookup("jdbc/Assignment2");
            Connection con = ds.getConnection();


            PreparedStatement pstmt = con.prepareStatement("SELECT * FROM BuyRecord WHERE LOGINNAME=? ORDER BY buytime DESC", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            pstmt.setString(1, loginName);
        
            ResultSet rs = pstmt.executeQuery();
            if (rs == null || !rs.next()) {
                purchaseRecord = null;
            } else {
                rs.last();
                int numberOfRecords = rs.getRow();
                rs.beforeFirst();
                purchaseRecord = new PurchaseRecord[numberOfRecords];
                for (int i = 0; rs.next(); i++) {
                    purchaseRecord[i] = new PurchaseRecord();
                    purchaseRecord[i].setSlotID(rs.getString("slotID"));
                    purchaseRecord[i].setRecordID(rs.getString("RECORDID"));
                    purchaseRecord[i].setMovieName(rs.getString("MOVIENAME"));
                    purchaseRecord[i].setPurchaseTime(rs.getString("BUYTIME"));
                    purchaseRecord[i].setAmount(rs.getString("SEATNO").length() / 3);
                    purchaseRecord[i].setTotalPrice(rs.getInt("MONEYUSED"));
                    purchaseRecord[i].setUsedLoyaltyPoint(rs.getInt("POINTUSED"));
                    purchaseRecord[i].setState(rs.getInt("RECORDSTATE"));
                }
            }
            
            if (rs != null) {
                rs.close(); //close ResultSet object
            }
            if (pstmt != null) {
                pstmt.close(); //close Statement object
            }
            if (con != null) {
                con.close(); //close Connection object 
            }//        } catch(NamingException n){
//            userObject = null;
        } catch (SQLException s) {
//            s.printStackTrace();
            purchaseRecord = null;
        } catch (NamingException c) {
//            c.printStackTrace();
            purchaseRecord = null;
        }
        return purchaseRecord;
    }
    
    public static PurchaseRecord getARecord(String recordID){
        PurchaseRecord purchaseRecord;
        try{
            
            Context initCtx = new InitialContext();
            Context envCtx = (Context)initCtx.lookup("java:comp/env");
            DataSource ds = (DataSource)envCtx.lookup("jdbc/Assignment2");
            Connection con = ds.getConnection();


            PreparedStatement pstmt = con.prepareStatement("SELECT * FROM BuyRecord WHERE RECORDID=?");
            pstmt.setString(1, recordID);
        
            ResultSet rs = pstmt.executeQuery();
            if (rs == null || !rs.next()) {
                purchaseRecord = null;
            } else {
                purchaseRecord = new PurchaseRecord();
                purchaseRecord.setRecordID(rs.getString("RECORDID"));
                purchaseRecord.setSlotID(rs.getString("slotID"));
                purchaseRecord.setMovieName(rs.getString("MOVIENAME"));
                purchaseRecord.setPurchaseTime(rs.getString("BUYTIME"));
                purchaseRecord.setAmount(rs.getString("SEATNO").length() / 3);
                purchaseRecord.setTotalPrice(rs.getInt("MONEYUSED"));
                purchaseRecord.setUsedLoyaltyPoint(rs.getInt("POINTUSED"));
                purchaseRecord.setState(rs.getInt("RECORDSTATE"));
            }
            
            if (rs != null) {
                rs.close(); //close ResultSet object
            }
            if (pstmt != null) {
                pstmt.close(); //close Statement object
            }
            if (con != null) {
                con.close(); //close Connection object 
            }//        } catch(NamingException n){
//            userObject = null;
        } catch (SQLException s) {
//            s.printStackTrace();
            purchaseRecord = null;
        } catch (NamingException c) {
//            c.printStackTrace();
            purchaseRecord = null;
        }
        return purchaseRecord;
    }
    
    public static PurchaseRecord[] getWaitingRefundRecord(){
        PurchaseRecord[] waitingRefundRecord;
        try{
            
            Context initCtx = new InitialContext();
            Context envCtx = (Context)initCtx.lookup("java:comp/env");
            DataSource ds = (DataSource)envCtx.lookup("jdbc/Assignment2");
            Connection con = ds.getConnection();

            PreparedStatement pstmt = con.prepareStatement("SELECT * FROM BuyRecord WHERE recordstate=3 ORDER BY buytime DESC", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        
            ResultSet rs = pstmt.executeQuery();
            if (rs == null || !rs.next()) {
                waitingRefundRecord = null;
            } else {
                rs.last();
                int numberOfRecords = rs.getRow();
                rs.beforeFirst();
                waitingRefundRecord = new PurchaseRecord[numberOfRecords];
                for (int i = 0; rs.next(); i++) {
                    waitingRefundRecord[i] = new PurchaseRecord();
                    waitingRefundRecord[i].setRecordID(rs.getString("RECORDID"));
                    waitingRefundRecord[i].setSlotID(rs.getString("slotID"));
                    waitingRefundRecord[i].setMovieName(rs.getString("MOVIENAME"));
                    waitingRefundRecord[i].setPurchaseTime(rs.getString("BUYTIME"));
                    waitingRefundRecord[i].setAmount(rs.getString("SEATNO").length() / 3);
                    waitingRefundRecord[i].setTotalPrice(rs.getInt("MONEYUSED"));
                    waitingRefundRecord[i].setUsedLoyaltyPoint(rs.getInt("POINTUSED"));
                    waitingRefundRecord[i].setState(rs.getInt("RECORDSTATE"));
                }
            }
            
            if (rs != null) {
                rs.close(); //close ResultSet object
            }
            if (pstmt != null) {
                pstmt.close(); //close Statement object
            }
            if (con != null) {
                con.close(); //close Connection object 
            }//        } catch(NamingException n){
//            userObject = null;
        } catch (SQLException s) {
//            s.printStackTrace();
            waitingRefundRecord = null;
        } catch (NamingException e) {
//            e.printStackTrace();
            waitingRefundRecord = null;
        }
        return waitingRefundRecord;    
    }
}
